How does MySQL internally optimize and cache results of deterministic functions?
Internal Optimization and Caching of Deterministic Functions in MySQL
MySQL can optimize the execution of deterministic functions because their output always depends only on their input values. This allows MySQL to cache or precompute results during query optimization and execution.
- 1
A deterministic function always returns the same result for the same input.
- 2
A non-deterministic function (e.g., NOW(), RAND()) may return different results on each call.
- 3
MySQL can only safely cache deterministic function results.
- 1
During query parsing, MySQL evaluates deterministic functions whose inputs are constants.
- 2
It replaces them with their computed literal values (constant folding).
- 3
This reduces repeated computation at runtime.
- 1
MySQL may compute a deterministic function once per row or once per statement depending on the context.
- 2
When the same expression appears multiple times in a query, MySQL avoids recomputing it.
- 3
Cached values may be reused within the same SELECT, JOIN, or WHERE clause.
- 1
User-defined stored functions can be declared DETERMINISTIC.
- 2
This allows the optimizer to apply constant folding and expression caching.
- 3
Without declaring DETERMINISTIC, MySQL assumes the function might change results and avoids optimizations.
- 1
Caching is only valid within the scope of a query—not across multiple queries.
- 2
MySQL does not maintain a global cache of function results.
- 3
Non-deterministic functions are never cached because doing so would break correctness.
In summary: MySQL optimizes deterministic functions through constant folding, expression caching, and use of the DETERMINISTIC keyword in stored functions. These optimizations reduce repeated computation and improve query performance.